/*
 * Copyright (c) 2018 maoyan.com
 * All rights reserved.
 *
 */
package com.darren.euc.util;

import com.alibaba.fastjson.JSON;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by caofangzi on 2016/12/1.
 */
public class ExcelToJson {

    private static final Logger LOGGER = LoggerFactory.getLogger(ExcelToJson.class);


    public String doExcelToJson(String filepath, int sheetIndex, int startRow, int... endRow) {
        List<Map<String, String>> list = new ArrayList<>();
        Sheet sheet = this.readSheet(filepath, sheetIndex);
        Row keyRow = sheet.getRow(0);
        int rowCount = endRow.length > 0 ? endRow[0] : sheet.getLastRowNum() + 1;
        for (int rowNum = startRow + 1; rowNum < rowCount; rowNum++) {
            Row row = sheet.getRow(rowNum);
            if (row == null || row.getCell(0) == null) {
                break;
            }
            Cell firstCell = row.getCell(0);
            firstCell.setCellType(Cell.CELL_TYPE_STRING);
            if (firstCell.getStringCellValue() == null || firstCell.getStringCellValue().length() == 0) {
                break;
            }
            Map<String, String> map = new HashMap<>();
            for (int cellNum = 0; cellNum < keyRow.getLastCellNum(); cellNum++) {
                if (keyRow.getCell(cellNum).getStringCellValue() == null || keyRow.getCell(cellNum).getStringCellValue().length() == 0) {
                    break;
                }
                if (row.getCell(cellNum) == null) {
                    map.put(keyRow.getCell(cellNum).getStringCellValue().split("-")[0], "");
                } else {
                    row.getCell(cellNum).setCellType(Cell.CELL_TYPE_STRING);
                    map.put(keyRow.getCell(cellNum).getStringCellValue().split("-")[0], row.getCell(cellNum).getStringCellValue());
                }
            }
            list.add(map);
        }
        return JSON.toJSONString(list);
    }

    private Sheet readSheet(String filepath, int sheetIndex) {
        boolean isExcel2007 = filepath.endsWith("xlsx");
        try (
                InputStream input = new FileInputStream(filepath);
                Workbook workbook = isExcel2007 ? new XSSFWorkbook(input) : new HSSFWorkbook(input);
        ) {
            return workbook.getSheetAt(sheetIndex);
        } catch (FileNotFoundException e) {
            LOGGER.error("文件找不到", e);
        } catch (IOException e) {
            LOGGER.error("io异常", e);
        }
        return null;
    }


    public static void main(String... args) {
        ExcelToJson excelToJson = new ExcelToJson();
        System.out.println(excelToJson.doExcelToJson("/Users/caofangzi/1.xlsx", 0, 0, 10));
    }
}